﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel;
using Ehost;
using System.Text.RegularExpressions;
using System.Web;
using System.IO;

namespace Get_testscript_Ehost
{
    public partial class Form1 : Form
    {
        XmlDocument Xdoc;
        public Form1()
        {
            InitializeComponent();
        }

     private void txtOpenFile_MouseClick(object sender, MouseEventArgs e)
        {
            OpenFileDialog fdialog = new OpenFileDialog();
            fdialog.Title = "Open XML Testsuite Files";
            fdialog.Filter = "XML File|*.xml";
            fdialog.InitialDirectory = @"C:\";
            fdialog.ShowDialog();

            txtOpenFile.Text = fdialog.FileName;
            lstTestSuite.Items.Clear();
        }

        private void btnAnalyseXML_Click(object sender, EventArgs e)
        {
            if (txtOpenFile.Text != null)
            {
                if (txtOpenFile.Text != "Click to open")
                {

                    Xdoc = new XmlDocument();
                    //Xdoc.LoadXml(txtOpenFile.Text.ToString());
                    Xdoc.Load(txtOpenFile.Text);

                    XmlNodeList nodelist = Xdoc.SelectNodes("//testsuites/testsuite");
                    lstTestSuite.Items.Clear();
                    foreach (XmlNode node in nodelist)
                    {
                        lstTestSuite.Items.Add(node.Attributes["name"].Value);
                        lstTestSuite.Items.Remove("Regression");
                        lstTestSuite.Items.Remove("SpotCheck");
                    }
                }
                else
                {
                    MessageBox.Show("Please select a xml file", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
            }
            else
            {
                MessageBox.Show("Please select a xml file", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        private void btnGenExcel_Click(object sender, EventArgs e)
        {
            if (txtOpenFile.Text != null)
            {
                if (txtOpenFile.Text != "Click to open")
                {
                    if (lstTestSuite.Items.Count != 0)
                    {
                        if (lstTestSuite.SelectedItem != null)
                        {
                            btnGenExcel.Text = "Generating...";
                            btnGenExcel.Enabled = false;
                            btnAnalyseXML.Enabled = false;
                            foreach (Object selecteditem in lstTestSuite.SelectedItems)
                            {
                                try
                                {

                                    //Get title and set output of script                
                                    //string output = @"C:\" + lstTestSuite.SelectedItem.ToString();
                                    //string title = lstTestSuite.SelectedItem.ToString();

                                    string title = selecteditem.ToString();
                                    //string output = @"C:\" + selecteditem.ToString();

                                    string output = selecteditem.ToString();

                                    string filePath = convertToExcel(title, output);
                                }
                                catch (Exception ex)
                                {
                                    MessageBox.Show("Cannot converto xml file to excel file: " + ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
                                }
                            }
                            btnGenExcel.Text = "Generate";
                            btnGenExcel.Enabled = true;
                            btnAnalyseXML.Enabled = true;
                        }
                        else
                        {
                            MessageBox.Show("Please select at least 01 script in listbox", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        }
                    }
                    else
                    {
                        MessageBox.Show("Please click \"Analyse XML\" button", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    }
                }
                else
                {
                    MessageBox.Show("Please select a xml file", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
            }
            else
            {
                MessageBox.Show("Please select a xml file", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        private void formatCell(Excel.Worksheet xlWorkSheet, int index)
        {

            #region Alignment for cell
            xlWorkSheet.get_Range("A" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            xlWorkSheet.get_Range("A" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            xlWorkSheet.get_Range("B" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("B" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            xlWorkSheet.get_Range("C" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("C" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            xlWorkSheet.get_Range("D" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("D" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            xlWorkSheet.get_Range("E" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("E" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            xlWorkSheet.get_Range("F" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("F" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            xlWorkSheet.get_Range("G" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("G" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            xlWorkSheet.get_Range("H" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("H" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            xlWorkSheet.get_Range("I" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("I" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            xlWorkSheet.get_Range("J" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("J" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            xlWorkSheet.get_Range("K" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("K" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            // Add applicable interfaces
            xlWorkSheet.get_Range("L" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("L" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            //add browser
            xlWorkSheet.get_Range("M" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("M" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

            xlWorkSheet.get_Range("N" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("N" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

            xlWorkSheet.get_Range("O" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("O" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

            xlWorkSheet.get_Range("P" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("P" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

            xlWorkSheet.get_Range("Q" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("Q" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

            xlWorkSheet.get_Range("R" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("S" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

            xlWorkSheet.get_Range("T" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("T" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

            xlWorkSheet.get_Range("U" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("U" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

            xlWorkSheet.get_Range("V" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("V" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

            xlWorkSheet.get_Range("W" + index).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
            xlWorkSheet.get_Range("W" + index).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            #endregion

            #region Wrap text for cell
            xlWorkSheet.Range["A" + index].WrapText = true;
            xlWorkSheet.Range["B" + index].WrapText = true;
            xlWorkSheet.Range["C" + index].WrapText = true;
            xlWorkSheet.Range["D" + index].WrapText = true;
            xlWorkSheet.Range["E" + index].WrapText = true;
            xlWorkSheet.Range["F" + index].WrapText = true;
            xlWorkSheet.Range["G" + index].WrapText = true;
            xlWorkSheet.Range["H" + index].WrapText = true;
            xlWorkSheet.Range["I" + index].WrapText = true;
            xlWorkSheet.Range["J" + index].WrapText = true;
            xlWorkSheet.Range["K" + index].WrapText = true;

            xlWorkSheet.Range["L" + index].WrapText = true;
            xlWorkSheet.Range["M" + index].WrapText = true;
            xlWorkSheet.Range["N" + index].WrapText = true;
            xlWorkSheet.Range["O" + index].WrapText = true;
            xlWorkSheet.Range["P" + index].WrapText = true;
            xlWorkSheet.Range["Q" + index].WrapText = true;

            xlWorkSheet.Range["R" + index].WrapText = true;
            xlWorkSheet.Range["S" + index].WrapText = true;
            xlWorkSheet.Range["T" + index].WrapText = true;
            xlWorkSheet.Range["U" + index].WrapText = true;
            xlWorkSheet.Range["V" + index].WrapText = true;
            xlWorkSheet.Range["W" + index].WrapText = true;
            // Add applicable interfaces
           // xlWorkSheet.Range["L" + index].WrapText = true;
            //add browser
           // xlWorkSheet.Range["M" + index].WrapText = true;
            #endregion

            #region Set border for cell
            xlWorkSheet.Range["A" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["B" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["C" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["D" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["E" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["F" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["G" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["H" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["I" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["J" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["K" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);

            xlWorkSheet.Range["L" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["M" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["N" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["O" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["P" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["Q" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);

            xlWorkSheet.Range["R" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["S" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["T" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["U" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["V" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Range["W" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            // Add applicable interfaces
            //xlWorkSheet.Range["L" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);

            //add browser
            //xlWorkSheet.Range["M" + index].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
            #endregion


        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

        public string convertToExcel(string titlescript, string output)
        {
            string filePath;
            string steps;
            string testSuiteName;
            string testScript;
            try
            {
                XmlHelper xml = new XmlHelper();
                xml.parseXml(txtOpenFile.Text);
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                object misValue = System.Reflection.Missing.Value;
                Excel.Range chartRange;
                int rowStart = 11;
                object oMissing = System.Reflection.Missing.Value;
                                       
                xlApp = new Excel.ApplicationClass();
               
                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

                //Add the statistics table
                xlWorkSheet.Cells[2,3]="Browser for Testing"; //C@
                xlWorkSheet.Cells[2, 4] = "IE 7";//D2
                xlWorkSheet.Cells[2, 5] = "IE 8";//....
                xlWorkSheet.Cells[2, 6] = "IE 9";
                xlWorkSheet.Cells[2, 7] = "IE 10";
                xlWorkSheet.Cells[2, 8] = "IE 11";
                xlWorkSheet.Cells[2, 9] = "Chrome";
                xlWorkSheet.Cells[2, 10] = "Firefox";
                xlWorkSheet.Cells[2, 11] = "Safari";//K2

                xlWorkSheet.Cells[3, 3] = "Total Test Cases";
                xlWorkSheet.Cells[4, 3] = "Total Test case Passed";
                xlWorkSheet.Cells[5, 3] = "Total Test Cases Failed";
                xlWorkSheet.Cells[6, 3] = "Total Test Cases Blocked";
                xlWorkSheet.Cells[7, 3] = "Total Test Cases Pending";
                xlWorkSheet.Cells[8, 3] = "Total Test case N/A";
                xlWorkSheet.Cells[9, 3] = "% Completed for each Browser";

                


                //end add

                xlWorkSheet.Cells[rowStart, 1] = "#";
                xlWorkSheet.Cells[rowStart, 2] = "TestLink ID";
                xlWorkSheet.Cells[rowStart, 3] = "Summary";
                xlWorkSheet.Cells[rowStart, 4] = "IE 7";
                xlWorkSheet.Cells[rowStart, 5] = "IE 8";
                xlWorkSheet.Cells[rowStart, 6] = "IE 9";
                xlWorkSheet.Cells[rowStart, 7] = "IE 10";
                xlWorkSheet.Cells[rowStart, 8] = "IE 11";
                xlWorkSheet.Cells[rowStart, 9] = "Chrome";
                xlWorkSheet.Cells[rowStart, 10] = "Firefox";
                xlWorkSheet.Cells[rowStart, 11] = "Safari";
                xlWorkSheet.Cells[rowStart, 12] = "Total time";
                xlWorkSheet.Cells[rowStart, 13] = "Tester Update";
                xlWorkSheet.Cells[rowStart, 14] = "Tester comment";
                xlWorkSheet.Cells[rowStart, 15] = "Applicable Interfaces";
                xlWorkSheet.Cells[rowStart, 16] = "Preconditions";
                xlWorkSheet.Cells[rowStart, 17] = "Actions";
                xlWorkSheet.Cells[rowStart, 18] = "Expected Result";
                xlWorkSheet.Cells[rowStart, 19] = "Priority";
                xlWorkSheet.Cells[rowStart, 20] = "Notes";
                xlWorkSheet.Cells[rowStart, 21] = "Jira Requirement ID";
                xlWorkSheet.Cells[rowStart, 22] = "Automated";
                xlWorkSheet.Cells[rowStart, 23] = "Test script";

                xlWorkSheet.get_Range("A11", "W11").Interior.Color = 65535;
                xlWorkSheet.get_Range("A11", "W11").Font.Bold = true;
                setColumnWidth(xlWorkSheet);
                xlWorkSheet.Name = "Test case";

                formatCell(xlWorkSheet, rowStart);

                // Get node script
                //XmlNodeList nodeList = xml.getNodeListByXpath("//testsuites/testsuite");
                XmlNodeList testSuiteList = Xdoc.SelectNodes("//testsuite[@name='" + titlescript + "']");

                int index = 12;
                int indexRow = 1;

                //for (int i = 0; i < nodeList.Count; i++)
                //{
                //testSuiteName = nodeList[i].Attributes["name"].Value;
                testSuiteName = titlescript;

                xlWorkSheet.Cells[index, 1] = testSuiteName;
                chartRange = xlWorkSheet.get_Range("A" + index, "W" + index);
                chartRange.Merge(false);
                chartRange.Interior.Color = 52613;
                chartRange.Font.Bold = true;
                formatCell(xlWorkSheet, index);
                index = index + 1;
                //XmlNodeList testSuiteList = xml.getNodeList(nodeList[i], "testsuite/testsuite");
                //for (int j = 0; j < testSuiteList.Count; j++)
                //{

                XmlNodeList testCaseList = xml.getNodeList(testSuiteList[0], "//testsuite/testcase");
                //testScript = testSuiteList[j].Attributes["name"].Value;
                testScript = titlescript;

                int totaltestcase = 0; //this variable use to count total testcase in a testsctipt
                for (int k = 0; k < testCaseList.Count; k++)
                {
                    xlWorkSheet.Cells[index, 1] = indexRow;
                    xlWorkSheet.Cells[index, 2] = "EHTC-" + xml.getTestLinkID(testCaseList[k]);

                    xlWorkSheet.Cells[index, 3] = xml.getSummary(testCaseList[k]);

                    //add list pass, failed ...to dropdown

                    var list = new List<string>();
                    list.Add("Passed");
                    list.Add("Failed");
                    list.Add("Blocked");
                    list.Add("Pending");
                    list.Add("N/A");

                    var flatList = string.Join(",", list.ToArray());

                    for (int x = 4; x <= 11; x++)
                       {
                        var cell = (Range)xlWorkSheet.Cells[index, x];
                        cell.Validation.Delete();
                        cell.Validation.Add(
                        XlDVType.xlValidateList,
                        XlDVAlertStyle.xlValidAlertInformation,
                        XlFormatConditionOperator.xlBetween,
                        flatList,
                        Type.Missing);

                        //format color
                        Excel.Range cellToSet = xlWorkSheet.get_Range("D"+index, "K"+index);
                        Excel.FormatConditions fcs = cellToSet.FormatConditions;

                        //Excel.FormatConditions fcs = xlWorkSheet.Cells[index, "M"].FormatConditions;

                        // Color for Passed
                        Excel.FormatCondition fc = (Excel.FormatCondition)fcs.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlEqual, "Passed", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                        //fc.Interior.Color = ColorTranslator.ToOle(Color.LightGreen);
                        fc.Interior.Color = 13561798;
                        //fc.Font.Color = 13561798;
                        //color for Failed
                        fc = (Excel.FormatCondition)fcs.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlEqual, "Failed", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                        //fc.Interior.Color = ColorTranslator.ToOle(Color.Red);
                        fc.Interior.Color = 13551615;
                       // fc.Font.Color = ColorTranslator.ToOle(Color.Red);
                        //color for Blocked
                        fc = (Excel.FormatCondition)fcs.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlEqual, "Blocked", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                        fc.Interior.Color = ColorTranslator.ToOle(Color.Orange);

                        //color for Pending
                        fc = (Excel.FormatCondition)fcs.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlEqual, "Pending", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                        fc.Interior.Color = ColorTranslator.ToOle(Color.Yellow);
                        //fc.Interior.PatternColor = "Neutral";

                        //color for N/A
                        fc = (Excel.FormatCondition)fcs.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlEqual, "N/A", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                        fc.Interior.Color = ColorTranslator.ToOle(Color.LightGray);
                        //

                        cell.Validation.IgnoreBlank = true;
                        cell.Validation.InCellDropdown = true;

                    }
                    
                   //end add list to dropdown
                   // Add applicable interfaces                    
                    xlWorkSheet.Cells[index, 15] = xml.getApplicableInterfaces(testCaseList[k]);
                    // End Add applicable interfaces
                    xlWorkSheet.Cells[index, 16] = xml.getPreconditions(testCaseList[k]);

                    xml.getSteps(testCaseList[k]);
                    steps = string.Empty;
                    string expected = string.Empty;
                    for (int l = 0; l < xml.lstSteps.Count; l++)
                    {
                        steps = steps == string.Empty ? steps + xml.lstSteps[l].ToString() : steps + "\n" + xml.lstSteps[l].ToString();
                        expected = expected == string.Empty ? expected + xml.lstExpected[l].ToString() : expected + "\n" + xml.lstExpected[l].ToString();
                    }
                    xlWorkSheet.Cells[index, 17] = steps;
                    xlWorkSheet.Cells[index, 18] = expected;
                    xlWorkSheet.Cells[index, 19] = xml.getImportant(testCaseList[k]); ;
                    xlWorkSheet.Cells[index, 20] = xml.getNotes(testCaseList[k]);
                    xlWorkSheet.Cells[index, 21] = xml.getJira(testCaseList[k]);
                    xlWorkSheet.Cells[index, 22] = xml.getAutomationStatus(testCaseList[k]);
                    xlWorkSheet.Cells[index, 23] = testScript;

                    formatCell(xlWorkSheet, index);
                    index = index + 1;
                    indexRow = indexRow + 1;

                    totaltestcase += 1;
                }

                //add total testcase into each browser
                xlWorkSheet.Cells[3, 4] = totaltestcase;//IE7
                xlWorkSheet.Cells[3, 5] = totaltestcase;//IE8
                xlWorkSheet.Cells[3, 6] = totaltestcase;//IE9
                xlWorkSheet.Cells[3, 7] = totaltestcase;//IE10
                xlWorkSheet.Cells[3, 8] = totaltestcase;//IE11
                xlWorkSheet.Cells[3, 9] = totaltestcase;//Chrome
                xlWorkSheet.Cells[3, 10] = totaltestcase;//Firefox
                xlWorkSheet.Cells[3, 11] = totaltestcase;//Safari

                //


                // add operator to count total passed, failed.... and % completed
                   //add to IE 7
                xlWorkSheet.Cells[4, 4] = "= COUNTIF(D13:D65000," + '"' + "Passed" + '"' + ')'; //IE7 Passed
                xlWorkSheet.Cells[5, 4] = "= COUNTIF(D13:D65000," + '"' + "Failed" + '"' + ')'; //IE7 Failed
                xlWorkSheet.Cells[6, 4] = "= COUNTIF(D13:D65000," + '"' + "Blocked" + '"' + ')'; //IE7 Blocked
                xlWorkSheet.Cells[7, 4] = "= COUNTIF(D13:D65000," + '"' + "Pending" + '"' + ')'; //IE7 Pending
                xlWorkSheet.Cells[8, 4] = "= COUNTIF(D13:D65000," + '"' + "N/A" + '"' + ')'; //IE7 N/A

                xlWorkSheet.Cells[9, 4] = "=(D4+D5)/D3";
                
                //Add to IE 8
                xlWorkSheet.Cells[4, 5] = "= COUNTIF(E13:E65000," + '"' + "Passed" + '"' + ')'; //IE7 Passed
                xlWorkSheet.Cells[5, 5] = "= COUNTIF(E13:E65000," + '"' + "Failed" + '"' + ')'; //IE7 Failed
                xlWorkSheet.Cells[6, 5] = "= COUNTIF(E13:E65000," + '"' + "Blocked" + '"' + ')'; //IE7 Blocked
                xlWorkSheet.Cells[7, 5] = "= COUNTIF(E13:E65000," + '"' + "Pending" + '"' + ')'; //IE7 Pending
                xlWorkSheet.Cells[8, 5] = "= COUNTIF(E13:E65000," + '"' + "N/A" + '"' + ')'; //IE7 N/A

                xlWorkSheet.Cells[9, 5] = "=(E4+E5)/E3";

                //Add to IE 9
                xlWorkSheet.Cells[4, 6] = "= COUNTIF(F13:F65000," + '"' + "Passed" + '"' + ')'; //IE7 Passed
                xlWorkSheet.Cells[5, 6] = "= COUNTIF(F13:F65000," + '"' + "Failed" + '"' + ')'; //IE7 Failed
                xlWorkSheet.Cells[6, 6] = "= COUNTIF(F13:F65000," + '"' + "Blocked" + '"' + ')'; //IE7 Blocked
                xlWorkSheet.Cells[7, 6] = "= COUNTIF(F13:F65000," + '"' + "Pending" + '"' + ')'; //IE7 Pending
                xlWorkSheet.Cells[8, 6] = "= COUNTIF(F13:F65000," + '"' + "N/A" + '"' + ')'; //IE7 N/A

                xlWorkSheet.Cells[9, 6] = "=(F4+F5)/F3";

                //Add to IE 10
                xlWorkSheet.Cells[4, 7] = "= COUNTIF(G13:G65000," + '"' + "Passed" + '"' + ')'; //IE7 Passed
                xlWorkSheet.Cells[5, 7] = "= COUNTIF(G13:G65000," + '"' + "Failed" + '"' + ')'; //IE7 Failed
                xlWorkSheet.Cells[6, 7] = "= COUNTIF(G13:G65000," + '"' + "Blocked" + '"' + ')'; //IE7 Blocked
                xlWorkSheet.Cells[7, 7] = "= COUNTIF(G13:G65000," + '"' + "Pending" + '"' + ')'; //IE7 Pending
                xlWorkSheet.Cells[8, 7] = "= COUNTIF(G13:G65000," + '"' + "N/A" + '"' + ')'; //IE7 N/A

                xlWorkSheet.Cells[9, 7] = "=(G4+G5)/G3";

                //Add to IE 11
                xlWorkSheet.Cells[4, 8] = "= COUNTIF(H13:H65000," + '"' + "Passed" + '"' + ')'; //IE7 Passed
                xlWorkSheet.Cells[5, 8] = "= COUNTIF(H13:H65000," + '"' + "Failed" + '"' + ')'; //IE7 Failed
                xlWorkSheet.Cells[6, 8] = "= COUNTIF(H13:H65000," + '"' + "Blocked" + '"' + ')'; //IE7 Blocked
                xlWorkSheet.Cells[7, 8] = "= COUNTIF(H13:H65000," + '"' + "Pending" + '"' + ')'; //IE7 Pending
                xlWorkSheet.Cells[8, 8] = "= COUNTIF(H13:H65000," + '"' + "N/A" + '"' + ')'; //IE7 N/A

                xlWorkSheet.Cells[9, 8] = "=(H4+H5)/H3";

                //Add to Chrome
                xlWorkSheet.Cells[4, 9] = "= COUNTIF(I13:I65000," + '"' + "Passed" + '"' + ')'; //IE7 Passed
                xlWorkSheet.Cells[5, 9] = "= COUNTIF(I13:I65000," + '"' + "Failed" + '"' + ')'; //IE7 Failed
                xlWorkSheet.Cells[6, 9] = "= COUNTIF(I13:I65000," + '"' + "Blocked" + '"' + ')'; //IE7 Blocked
                xlWorkSheet.Cells[7, 9] = "= COUNTIF(I13:I65000," + '"' + "Pending" + '"' + ')'; //IE7 Pending
                xlWorkSheet.Cells[8, 9] = "= COUNTIF(I13:I65000," + '"' + "N/A" + '"' + ')'; //IE7 N/A

                xlWorkSheet.Cells[9, 9] = "=(I4+I5)/I3";

                //Add to Firefox
                xlWorkSheet.Cells[4, 10] = "= COUNTIF(J13:J65000," + '"' + "Passed" + '"' + ')'; //IE7 Passed
                xlWorkSheet.Cells[5, 10] = "= COUNTIF(J13:J65000," + '"' + "Failed" + '"' + ')'; //IE7 Failed
                xlWorkSheet.Cells[6, 10] = "= COUNTIF(J13:J65000," + '"' + "Blocked" + '"' + ')'; //IE7 Blocked
                xlWorkSheet.Cells[7, 10] = "= COUNTIF(J13:J65000," + '"' + "Pending" + '"' + ')'; //IE7 Pending
                xlWorkSheet.Cells[8, 10] = "= COUNTIF(J13:J65000," + '"' + "N/A" + '"' + ')'; //IE7 N/A

                xlWorkSheet.Cells[9, 10] = "=(J4+J5)/J3";

                //Add to Safari
                xlWorkSheet.Cells[4, 11] = "= COUNTIF(K13:K65000," + '"' + "Passed" + '"' + ')'; //IE7 Passed
                xlWorkSheet.Cells[5, 11] = "= COUNTIF(K13:K65000," + '"' + "Failed" + '"' + ')'; //IE7 Failed
                xlWorkSheet.Cells[6, 11] = "= COUNTIF(K13:K65000," + '"' + "Blocked" + '"' + ')'; //IE7 Blocked
                xlWorkSheet.Cells[7, 11] = "= COUNTIF(K13:K65000," + '"' + "Pending" + '"' + ')'; //IE7 Pending
                xlWorkSheet.Cells[8, 11] = "= COUNTIF(K13:K65000," + '"' + "N/A" + '"' + ')'; //IE7 N/A

                xlWorkSheet.Cells[9, 11] = "=(K4+K5)/K3";
                //           

                //Set border and font for statistics table
                xlWorkSheet.get_Range("C2", "K9").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                xlWorkSheet.get_Range("C2", "K9").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                xlWorkSheet.get_Range("D9", "K9").Style = "Percent";

                for (int y = 2; y <= 9; y++)
                {
                    xlWorkSheet.Range["C" + y].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
                    xlWorkSheet.Range["D" + y].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
                    xlWorkSheet.Range["E" + y].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
                    xlWorkSheet.Range["F" + y].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
                    xlWorkSheet.Range["G" + y].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
                    xlWorkSheet.Range["H" + y].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
                    xlWorkSheet.Range["I" + y].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
                    xlWorkSheet.Range["J" + y].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
                    xlWorkSheet.Range["K" + y].BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);

                }

                xlWorkSheet.get_Range("C2", "K9").Font.Bold = true;
                xlWorkSheet.get_Range("D3", "K9").Font.Bold = false;

                xlWorkSheet.get_Range("C2", "K2").Interior.Color = 52613;
                xlWorkSheet.get_Range("C3", "C9").Interior.Color = 52613;
                
                

                //end set border and font for statistics table

                xlWorkSheet.get_Range("A11", "W11").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                xlWorkSheet.get_Range("A11", "W11").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;

               
                // Trim the invalid filename of excel file
                output = trimInvalidFilename(output);

                filePath = @"C:\" + output + "_Ehost_Testcase.xlsx";
                xlWorkBook.SaveAs(filePath, Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

                          
                xlApp.Quit();

                releaseObject(xlApp);
                releaseObject(xlWorkBook);
                releaseObject(xlWorkSheet);

            }
            catch (Exception ex)
            {
                throw;
            }

            return filePath;

        }

        
       private void setColumnWidth(Excel.Worksheet xlWorkSheet)
        {
            ((Excel.Range)xlWorkSheet.Columns["A"]).ColumnWidth = 10;
            ((Excel.Range)xlWorkSheet.Columns["B"]).ColumnWidth = 20;
            ((Excel.Range)xlWorkSheet.Columns["C"]).ColumnWidth = 40;
            ((Excel.Range)xlWorkSheet.Columns["O"]).ColumnWidth = 30;
            ((Excel.Range)xlWorkSheet.Columns["P"]).ColumnWidth = 40;
            ((Excel.Range)xlWorkSheet.Columns["Q"]).ColumnWidth = 40;
            ((Excel.Range)xlWorkSheet.Columns["R"]).ColumnWidth = 40;
            ((Excel.Range)xlWorkSheet.Columns["S"]).ColumnWidth = 20;
            ((Excel.Range)xlWorkSheet.Columns["T"]).ColumnWidth = 20;
            ((Excel.Range)xlWorkSheet.Columns["U"]).ColumnWidth = 20;
            ((Excel.Range)xlWorkSheet.Columns["V"]).ColumnWidth = 10;
            ((Excel.Range)xlWorkSheet.Columns["W"]).ColumnWidth = 30;
            // Add applicable interfaces
            //((Excel.Range)xlWorkSheet.Columns["L"]).ColumnWidth = 30;
        }

        public string trimInvalidFilename(string filename)
        {
            string regexSearch = new string(Path.GetInvalidFileNameChars()) + new string(Path.GetInvalidPathChars());
            Regex r = new Regex(string.Format("[{0}]", Regex.Escape(regexSearch)));
            filename = r.Replace(filename, "");

            return filename;
        }

        private void txtOpenFile_TextChanged(object sender, EventArgs e)
        {

        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

    }
}
